package DB_Layer;

import java.sql.*;
import java.util.ArrayList;

import Model_Layer.Customer;
import Model_Layer.Supplier;
import Model_Layer.Zipcode_City;

/***
 * DB_customer.java
 * UCN dmaa0214 - October 2014 
 * 
 * @author Group 5 
 * @version 1.0
 * 
 */
@SuppressWarnings("unused")
public class DB_Customer implements IFDB_Customer{
	private Connection con;
		
	public DB_Customer() {
		con = DbConnection.getInstance().getDBcon();
	}
	
	/*
	 *  public methods
	 */
	
	public int insertCustomer(Customer cus) throws Exception {
						
		int nextCusID = GetMaxID.getMaxID("SELECT max(cusID) from Customer");
		nextCusID = nextCusID + 1;
		System.out.println("Next customer ID = " + nextCusID);
		
		int rc = -1;
		String query = "INSERT INTO Customer(CusID, Name, Address, PhoneNo, Email, Private_Business, Discount, Country, ZipCode) " + 
		   " VALUES('" + 
		
		// customer fields
		nextCusID + "','" + 
		cus.getName() + "','" + 
		cus.getAddress() + "','" + 
		cus.getPhoneNo() + "','" + 
		cus.getEmail() + "','" + 
		cus.getPrivate_Business() + "','" + 
		cus.getDiscount() + "','" +
		cus.getCountry() + "','" + 
		cus.getZipCode() + "');";
		
		// postcode / city 
		query +=  " INSERT INTO ZipCode_City (ZipCode, City) VALUES('" +
			cus.getZipCode() + "','" +  
			cus.getCity() + "');";
								
		System.out.println("Insert : " + query);
		
		try {
			Statement statement = con.createStatement();
			statement.setQueryTimeout(5);
			rc = statement.executeUpdate(query);
			statement.close();
		}
		catch(Exception e) {
			System.out.println("Customer was not created! ");
			throw new Exception ("Customer was not inserted corrrectly! ");
		}
		
		return(rc);
	}
	
	public int updateCustomer(Customer cus) {
		
		Customer cusObj = cus;
		int rc = -1;
		
		String query = "UPDATE Customer SET " + 
				"CusID = '" + cusObj.getCusID() + "', " + 
				"Name = '" + cusObj.getName() + "', " + 
				"Address = '" + cusObj.getAddress() + "', " + 
				"PhoneNo = '" + cusObj.getPhoneNo() + "', " + 
				"Email = '" + cusObj.getEmail() + "', " +
				"Private_Business = '" + cusObj.getPrivate_Business() + "', " + 
				"Discount = '" + cusObj.getDiscount() + "', " +
				"ZipCode = '" + cusObj.getZipCode() + "'" + 
				"WHERE CusID = '" + cusObj.getCusID() + "'; " +
		
				"INSERT INTO ZipCode_City (Zipcode, City) values(" + cusObj.getZipCode() + 
				", '" + cusObj.getCity() + "');";
		
		System.out.println("Update query: " + query);
		
		try {
			Statement statement = con.createStatement();
			statement.setQueryTimeout(5);
			rc = statement.executeUpdate(query);
			statement.close();
		}
		catch(Exception e) {
			System.out.println("Update exception in customer db: " + e);
		}
		
		return(rc);
	}
	
	public Customer getCustomer(int cusID, boolean retriveAssociation) {
		String wClause = "cusID = '" + cusID + "'"; 
		return singleWhere(wClause, true);
	}
	
	public int deleteCustomer(int cusID) {
		
		int rc = -1;
		
		String query = "DELETE FROM Customer WHERE CusID = '" + cusID + "'";
		System.out.println(query);		
				
		try {
			Statement statement = con.createStatement();
			statement.setQueryTimeout(5);
			rc = statement.executeUpdate(query);
			statement.close();
		}
		catch(Exception e) {
			System.out.println("Delete exception in customer db: " + e);
		}
		
		return(rc);
	}
	
	
	public ArrayList<Customer> getAllCustomers() {
		return miscWhere("", false);
	}
		
	
	/*
	 * private methods
	 */
	
    //michWere is used whenever we want to select more than one customer
	private ArrayList<Customer> miscWhere(String wClause, boolean retrieveAssociation) {
		
		ResultSet results;
		ArrayList<Customer> list = new ArrayList<Customer>();
		
		String query = buildQuery(wClause);
		
		try {
			Statement statement = con.createStatement();
			statement.setQueryTimeout(5);
			results = statement.executeQuery(query);
		
		
			while(results.next()) {
				Customer cusObj = new Customer();
				cusObj = buildCustomer(results);
				list.add(cusObj);
			}
			
			statement.close();
			
			//if(retrieveAssociation) {
				
				for(Customer cusObj : list) {
					int findZipCode = cusObj.getZipCode();
					String city = getCityFromZipCode(findZipCode);
					cusObj.setCity(city);		
					
				}		
			//}	
		}	
			
		catch(Exception e) {
			System.out.println("Query Exception - Select: " + e);
			e.printStackTrace();
		}
		
		return list;
	}
	

	
	//Singelwhere is used when we only select one customer
	private Customer singleWhere(String wClause, boolean retrieveAssociation) {
		
		ResultSet results;
		Customer cusObj = new Customer();
		
		String query = buildQuery(wClause);
		// System.out.println(query);
		
		try {
			Statement statement = con.createStatement();
			statement.setQueryTimeout(5);
			results = statement.executeQuery(query);
			
			if(results.next()) {
				
				cusObj = buildCustomer(results);
				statement.close();		
				
				//if(retrieveAssociation) {
					int zipcode = cusObj.getZipCode();
					String city = getCityFromZipCode(zipcode);
					cusObj.setCity(city);
				//}
			}
			else {
				cusObj = null;
			}
               
		}
		catch(Exception e) {
			System.out.println("Query Exception: " + e);
		}
		
		return cusObj;
	}
	
	// method to build the query
	private String buildQuery(String wClause) {
		
		String query = "SELECT CusID, Name, Address, PhoneNo, Email, Private_Business, "
				+ "Discount, Country, ZipCode from Customer";
		
		if (wClause.length() > 0) 
			query = query + " WHERE " + wClause;
		
		return query;
	}
	
	
	// method to build a customer object
	private Customer buildCustomer(ResultSet results) {
		
		Customer cusObj = new Customer();
		//Zipcode_City zipcodeCityObj = new Zipcode_City();

		// Columns for table Customer are used
		try {
			cusObj.setCusID(results.getInt("CusID"));
			cusObj.setName(results.getString("Name"));
			cusObj.setAddress(results.getString("Address"));
			cusObj.setPhoneNo(results.getString("PhoneNo"));
			cusObj.setEmail(results.getString("Email"));
			cusObj.setDiscount(results.getFloat("Discount"));
			cusObj.setCountry(results.getString("Country"));
			
						
			// set up zipcode and city
			//zipcodeCityObj.setZipcode(results.getInt("Zipcode"));
			//zipcodeCityObj.setCity(results.getString("City"));
		}
		catch(Exception e) {
			System.out.println("Error in building the customer object: " + e);
		}
				
		return cusObj;
	}
	
	private String getCityFromZipCode(int zipcode) {
		String query = "SELECT City FROM Zipcode_City WHERE Zipcode = '" + zipcode + "';";
		
		String foundCity = null;
		
		try {
			Statement statement = con.createStatement();
			statement.setQueryTimeout(5);
			ResultSet cityresult = statement.executeQuery(query);
			
			foundCity = cityresult.getString("City");
			
			statement.close();
		}
		catch(Exception e) {
			e.getStackTrace();
		}
		
		return foundCity;
	}

		
}
